package doughnut.xc.controller;

import cn.afterturn.easypoi.csv.CsvExportUtil;
import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.word.WordExportUtil;
import com.opencsv.CSVWriter;
import com.zaxxer.hikari.HikariDataSource;
import doughnut.xc.mapper.ResourceMapper;
import doughnut.xc.mapper.User2Mapper;
import doughnut.xc.mapper.UserMapper;
import doughnut.xc.pojo.Resource;
import doughnut.xc.pojo.User;
import doughnut.xc.pojo.User2;
import doughnut.xc.service.UserService;
import doughnut.xc.utils.EntityUtils;
import doughnut.xc.utils.ExcelExportEngine;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.jasperreports.engine.*;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import tk.mybatis.mapper.entity.Example;

import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/user")
public class UserController {

    private static final SimpleDateFormat SIMPLE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");

    @Autowired
    private UserService userService;

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private User2Mapper user2Mapper;

    @Autowired
    private ResourceMapper resourceMapper;

    @Autowired
    private HikariDataSource hikariDataSource;

    @GetMapping("/findPage")
    public List<User>  findPage(
            @RequestParam(value = "page",defaultValue = "1") Integer page,
            @RequestParam(value = "rows",defaultValue = "10") Integer pageSize){
        return userService.findPage(page,pageSize);
    }

    @RequestMapping(value = "/downLoadXlsByJxl", name = "使用JXL操作Excel")
    public void downLoadXlsByJxl(HttpServletResponse response) throws Exception{
        // 1、获取输出流
        ServletOutputStream outputStream = response.getOutputStream();
        // 2、创建可写入的Excel工作薄
        WritableWorkbook workbook= Workbook.createWorkbook(outputStream);
        // 3、创建工作表创建
        WritableSheet sheet= workbook.createSheet("sheet1", 0);
        // 4、创建单元格
        String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
        Label label = null;
        for (int i = 0; i < titles.length; i++) {
            label = new Label(i, 0 , titles[i]); // 列脚标 行脚表 内容
            sheet.addCell(label);
        }
        // 调整列宽
        sheet.setColumnView(2, 15); // 参数1：列脚标 参数2：一个标准字母的宽度
        sheet.setColumnView(3, 50); // 参数1：列脚标 参数2：一个标准字母的宽度
        sheet.setColumnView(4, 30); // 参数1：列脚标 参数2：一个标准字母的宽度

        // 获取处理内容
        List<User> users = userMapper.selectAll();

        int rowIndex = 1;
        for (User user : users) {
            label = new Label(0, rowIndex , user.getId().toString());
            sheet.addCell(label);
            label = new Label(1, rowIndex , user.getUserName());
            sheet.addCell(label);
            label = new Label(2, rowIndex , user.getPhone());
            sheet.addCell(label);
            label = new Label(3, rowIndex , user.getHireDate().toString());
            sheet.addCell(label);
            label = new Label(4, rowIndex , user.getAddress());
            sheet.addCell(label);
            rowIndex++;
        }
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename="
                + new String("JXL测试.xls".getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        // 5、写入数据
        workbook.write();
        // 6、释放资源
        workbook.close();
        outputStream.close();
    }

    @RequestMapping(value = "/uploadExcel", name = "上传用户数据")
    public void uploadExcel(@RequestBody MultipartFile file) throws Exception {
        // 读取文件
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        // 获取工作薄中的第一个工作表
        XSSFSheet sheet = workbook.getSheetAt(0);
        //获取这个sheet中最后一行数据，为了循环遍历
        int lastRowIndex = sheet.getLastRowNum();

        User user = null;
        for (int i = 1; i <= lastRowIndex; i++) {
            XSSFRow row = sheet.getRow(i);
            user = new User();
            String username = row.getCell(0).getStringCellValue();
            user.setUserName(username);
            String phone = row.getCell(1).getStringCellValue();
            user.setPhone(phone);
            String province = row.getCell(2).getStringCellValue();
            user.setProvince(province);
            String city = row.getCell(3).getStringCellValue();
            user.setCity(city);
            Integer salary = ((Double) row.getCell(4).getNumericCellValue()).intValue();
            user.setSalary(salary);
            String hireDate = row.getCell(5).getStringCellValue();
            user.setHireDate(SIMPLE_FORMAT.parse(hireDate));
            String birthday = row.getCell(6).getStringCellValue();
            user.setBirthday(SIMPLE_FORMAT.parse(birthday));
            String address = row.getCell(7).getStringCellValue();
            user.setAddress(address);

            userMapper.insert(user);
        }
        workbook.close();
    }

    @RequestMapping(value = "/downLoadXlsxByPoi", name = "使用POI导出数据")
    public void downLoadXlsxByPoi(HttpServletResponse response) throws Exception {
        // 基本的导出
        // poiBasicExport(response);
        // 带样式的导出
        // poiWithStyleExport(response);
        // 使用模版进行导出
         poiWithTemplateExport(response);
    }

    @RequestMapping(value = "/download", name = "使用POI导出用户详细数据")
    public void download(Long id, HttpServletResponse response) throws Exception {
        // 通过编码的方式一：
        // poiCodeWay(id, response);
        // 通过自定义模版的方式二：
        // poiCustomTemWay(id, response);
        // 使用EasyPOI模版方式三：
        // easyPOITemplateWay(id, response);
        // 使用jasper-pdf模版方式导出
        this.downLoadWithJasper(id, response);
    }

    @RequestMapping(value = "/downLoadMillion", name = "百万数据导出")
    public void downLoadMillion(HttpServletResponse response) throws Exception {
        SXSSFWorkbook workbook = new SXSSFWorkbook(200);

        SXSSFSheet sheet = null;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Cell cell = null;
        Row row = null;
        User2 user = null;
        int pageNum = 1;
        int rowIndex = 1;
        int pageSize = 50000;
        // 记录导出的总量是多少
        int sumNum = 0;
        // 标题
        String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
        while (true) {
            // 每次查询5w数据
            List<User2> userList = user2Mapper.findPage((pageNum - 1) * pageSize, pageSize);
            if (userList == null || userList.size() <= 0) {
                break;
            }
            // 什么时候创建工作表 0 1000000 2000000 3000000 4000000 5000000
            if (sumNum % 1000000 == 0) {
                sheet = workbook.createSheet("第" + ((sumNum / 1000000) +1) + "个100W");
                sheet.setColumnWidth(0,8*256);
                sheet.setColumnWidth(1,12*256);
                sheet.setColumnWidth(2,15*256);
                sheet.setColumnWidth(3,15*256);
                sheet.setColumnWidth(4,30*256);
                // 创建标题行
                Row titleRow = sheet.createRow(0);
                for (int i = 0; i < titles.length; i++) {
                    cell = titleRow.createCell(i);
                    cell.setCellValue(titles[i]);
                }
                rowIndex = 1;
            }
            System.out.println(sumNum);

            for (int i = 0; i < userList.size(); i++) {
                user = userList.get(i);
                row = sheet.createRow(rowIndex);
                cell = row.createCell(0);
                cell.setCellValue(user.getId());
                cell = row.createCell(1);
                cell.setCellValue(user.getUserName());
                cell = row.createCell(2);
                cell.setCellValue(user.getPhone());
                cell = row.createCell(3);
                cell.setCellValue(simpleDateFormat.format(user.getHireDate()));
                cell = row.createCell(4);
                cell.setCellValue(user.getAddress());
                rowIndex++;
            }
            sumNum+=userList.size();
            pageNum++;
        }
        String fileName = "百万数据导出.xlsx";
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        // 写入数据
        workbook.write(outputStream);
        // 释放资源
        outputStream.close();
        workbook.close();
    }

    @RequestMapping(value = "/downLoadCSV", name = "csv文件百万数据导出")
    public void downLoadCSV(HttpServletResponse response) throws Exception {
        // 使用csv文件方式导出
        // downLoadCSVWay(response);
        // 使用easyPOI导出csv文件
        downLoadEasyPOIWay(response);
    }

    @RequestMapping("/{id}")
    public User findById(@PathVariable Long id) {
        User user = new User();
        user.setId(id);
        user = userMapper.selectOne(user);

        Resource resource = new Resource();
        resource.setUserId(id);
        List<Resource> resourceList = resourceMapper.select(resource);
        user.setResourceList(resourceList);
        return user;
    }

    @RequestMapping(value = "/downloadContract", name = "POI操作word-下载合同")
    public void downloadContract(Long id, HttpServletResponse response) throws Exception {
        // 使用poi方式操作word
        // downloadContractByPOI(id, response);
        // 使用easyPOI方式操作 word
        downloadContractByEasyPOI(id, response);
    }

    @RequestMapping(value = "/downLoadPDF", name = "pdf导出")
    public void downLoadPDF(HttpServletResponse response) throws Exception {
        // 直接数据库导出列表
        // this.downLoadByDB(response);
        // 完成数据从后台导出
        // this.downLoadByBackEnd(response);
        // 使用 jasper分组形式导出pdf
        downLoadXlsxByJasperGroup(response);
    }

    @RequestMapping(value = "/downLoadWithEasyPOI", name = "ExcelPOI导出")
    public void downLoadWithEasyPOI(HttpServletResponse response) throws Exception {
        // 使用easyPOi方式导出
         downLoadWithEasyPOIWay(response);
    }

    // 向单元格中写入图片
    private void setCellImage(XWPFTableCell cell, File imageFile) {
        XWPFRun run = cell.getParagraphs().get(0).createRun();
        try (FileInputStream fis = new FileInputStream(imageFile)) {
            run.addPicture(fis, XWPFDocument.PICTURE_TYPE_JPEG, imageFile.getName(), Units.toEMU(100), Units.toEMU(50));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 用于深克隆行
    private void copyRow(XWPFTable xwpfTable, XWPFTableRow sourceRow, int rowIndex) {
        XWPFTableRow targetRow = xwpfTable.insertNewTableRow(rowIndex);
        targetRow.getCtRow().setTrPr(sourceRow.getCtRow().getTrPr());
        // 获取源行的单元格
        List<XWPFTableCell> cells = sourceRow.getTableCells();
        if(CollectionUtils.isEmpty(cells)){
            return;
        }
        XWPFTableCell targetCell = null;
        for (XWPFTableCell cell : cells) {
            targetCell = targetRow.addNewTableCell();
            // 附上单元格的样式
            // 单元格的属性
            targetCell.getCTTc().setTcPr(cell.getCTTc().getTcPr());
            targetCell.getParagraphs().get(0).getCTP().setPPr(cell.getParagraphs().get(0).getCTP().getPPr());
        }
    }

    private void poiCustomTemWay(Long id, HttpServletResponse response) throws Exception {
        // 读取自定义模版文件
        File file = ResourceUtils.getFile("classpath:excel_template/custom_userInfo.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        // 获取对象
        User user = new User();
        user.setId(id);
        user = userMapper.selectOne(user);
        workbook = ExcelExportEngine.write2Excel(user, workbook);

        String fileName = "POI自定义模版用户详细数据.xlsx";
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        // 写入数据
        workbook.write(outputStream);
        // 释放资源
        outputStream.close();
        workbook.close();
    }

    private void poiCodeWay(Long id, HttpServletResponse response) throws IOException, InvalidFormatException {
        File file = ResourceUtils.getFile("classpath:excel_template/userInfo.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);

        User user = new User();
        user.setId(id);
        user = userMapper.selectOne(user);
        // 用户名
        sheet.getRow(1).getCell(1).setCellValue(user.getUserName());
        // 手机号
        sheet.getRow(2).getCell(1).setCellValue(user.getPhone());
        // 生日
        sheet.getRow(3).getCell(1).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(user.getBirthday()));
        // 工资
        sheet.getRow(4).getCell(1).setCellValue(user.getSalary());
        // 入职日期
        sheet.getRow(5).getCell(1).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(user.getHireDate()));
        // 现住址
        sheet.getRow(7).getCell(1).setCellValue(user.getAddress());
        // 省份
        sheet.getRow(6).getCell(1).setCellValue(user.getProvince());
        // 城市
        sheet.getRow(6).getCell(3).setCellValue(user.getCity());
        // 司龄  CONCATENATE(DATEDIF(B6,TODAY(),"Y"),"年",DATEDIF(B6,TODAY(),"YM"),"个月")
        // 可以使用excel的函数 计算
        // 方式一：
        // sheet.getRow(5).getCell(3).setCellFormula("CONCATENATE(DATEDIF(B6,TODAY(),\"Y\"),\"年\",DATEDIF(B6,TODAY(),\"YM\"),\"个月\")");
        // 方式二：直接写在模版里面

        // 图片
        String photoPath = user.getPhoto();
        // 先创建一个字节输出流
        ByteArrayOutputStream os = new ByteArrayOutputStream();

        // 读取图片
        File photoFile = ResourceUtils.getFile("classpath:" + photoPath);
        // BufferedImage是一个带缓冲区图像类,主要作用是将一幅图片加载到内存中
        BufferedImage bufferedImage = ImageIO.read(photoFile);
        // 把读取到图像放入到输出流中
        ImageIO.write(bufferedImage, "JPG", os); // ！！！"JPG"这个地方不能写死 可以优化！！！
        // 创建一个绘图控制类，负责画图
        Drawing<XSSFShape> patriarch = sheet.createDrawingPatriarch();
        // 指定把图片放到哪个位置
        // int dx1, int dy1, int dx2, int dy2, int col1(开始列), int row1(开始行), int col2(结束列), int row2(结束行)
        // 最后两个参数 都要比原有的大一位才可以
        // 前面参数是英氏单位  1cm=36000
        ClientAnchor anchor = patriarch.createAnchor(0, 0, 0, 0, 2, 1, 4, 5);
        // 开始把图片写入到sheet指定的位置
        patriarch.createPicture(anchor,
                workbook.addPicture(os.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG)); // PICTURE_TYPE_JPEG写死了！！！

        String fileName = "POI模版用户详细数据.xlsx";
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        // 写入数据
        workbook.write(outputStream);
        // 释放资源
        outputStream.close();
        workbook.close();
    }

    private void poiWithTemplateExport(HttpServletResponse response) throws Exception {
        File file = ResourceUtils.getFile("classpath:excel_template/userTemplate.xlsx");
        // 根据模版创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);

        // 获取处理内容
        List<User> users = userMapper.selectAll();
        // 获取模版sheet2中的样式 作为标椎
        XSSFCellStyle contentTemplateStyle = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();
        Row row = null;
        Cell cell = null;
        int rowIndex = 2;
        for (User user : users) {
            row = sheet.createRow(rowIndex);
            cell = row.createCell(0);
            cell.setCellValue(user.getId());
            cell.setCellStyle(contentTemplateStyle);

            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());
            cell.setCellStyle(contentTemplateStyle);

            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(contentTemplateStyle);

            cell = row.createCell(3);
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(user.getHireDate()));
            cell.setCellStyle(contentTemplateStyle);

            cell = row.createCell(4);
            cell.setCellValue(user.getAddress());
            cell.setCellStyle(contentTemplateStyle);
            rowIndex++;
        }
        // 删除sheet1
        workbook.removeSheetAt(1);

        String fileName = "POI模版测试.xlsx";
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        // 写入数据
        workbook.write(outputStream);
        // 释放资源
        outputStream.close();
        workbook.close();

    }

    private void poiWithStyleExport(HttpServletResponse response) throws Exception {
        // 创建全新的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 全新的工作表
        XSSFSheet sheet = workbook.createSheet("用户数据");
        // 设置列宽
        sheet.setColumnWidth(0,5*256); // 填写1就是一个标准字母的宽度 256分之一
        sheet.setColumnWidth(1,8*256);
        sheet.setColumnWidth(2,15*256);
        sheet.setColumnWidth(3,15*256);
        sheet.setColumnWidth(4,30*256);

        /* ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ 大标题 ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ */
        // 大标题
        XSSFRow bigTitleRow = sheet.createRow(0);
        // 行高
        bigTitleRow.setHeightInPoints(42);
        // 字体
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 18);
        // 字体加粗
        font.setBold(true);
        // 合并单元格
        // int firstRow 起始行, int lastRow 结束行, int firstCol 起始列, int lastCol 结束列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
        // 获取样式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        cellStyle.setFont(font);
        // 对齐方式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 填充数据
        XSSFCell cell = bigTitleRow.createCell(0);
        cell.setCellValue("用户数据");
        cell.setCellStyle(cellStyle);
        /* ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ 大标题 ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ */

        /* ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ 小标题 ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ */
        String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
        XSSFRow row = sheet.createRow(1);
        // 行高
        row.setHeightInPoints(25);

        XSSFCellStyle liCellStyle = workbook.createCellStyle();
        // 复用字体
        liCellStyle.cloneStyleFrom(cellStyle);

        XSSFFont liFont = workbook.createFont();
        liFont.setFontName("华文行楷");
        liFont.setFontHeightInPoints((short)12);
        liCellStyle.setFont(liFont);
        // 处理标题
        for (int i = 0; i < titles.length; i++) {
            XSSFCell liCell = row.createCell(i);
            liCell.setCellValue(titles[i]);
            liCell.setCellStyle(liCellStyle);
        }
        /* ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ 小标题 ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ */
        // 获取处理内容
        List<User> users = userMapper.selectAll();
        int rowIndex = 2;
        for (User user : users) {
            row = sheet.createRow(rowIndex);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getUserName());
            row.createCell(2).setCellValue(user.getPhone());
            row.createCell(3).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(user.getHireDate()));
            row.createCell(4).setCellValue(user.getAddress());
            rowIndex++;
        }

        String fileName = "POI测试.xlsx";
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        // 5、写入数据
        workbook.write(outputStream);
        // 6、释放资源
        outputStream.close();
        workbook.close();
    }

    private void poiBasicExport(HttpServletResponse response) throws IOException {
        // 创建全新的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 全新的工作表
        XSSFSheet sheet = workbook.createSheet("用户数据");
        // 设置列宽
        sheet.setColumnWidth(0,5*256); // 填写1就是一个标准字母的宽度 256分之一
        sheet.setColumnWidth(1,8*256);
        sheet.setColumnWidth(2,15*256);
        sheet.setColumnWidth(3,15*256);
        sheet.setColumnWidth(4,30*256);
        // 创建标题行
        XSSFRow row = sheet.createRow(0);
        String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};

        for (int i = 0; i < titles.length; i++) {
            row.createCell(i).setCellValue(titles[i]);
        }
        // 获取处理内容
        List<User> users = userMapper.selectAll();
        int rowIndex = 1;
        for (User user : users) {
            row = sheet.createRow(rowIndex);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getUserName());
            row.createCell(2).setCellValue(user.getPhone());
            row.createCell(3).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(user.getHireDate()));
            row.createCell(4).setCellValue(user.getAddress());
            rowIndex++;
        }

        String fileName = "POI测试.xlsx";
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        // 5、写入数据
        workbook.write(outputStream);
        // 6、释放资源
        outputStream.close();
        workbook.close();
    }

    private void easyPOITemplateWay(Long id, HttpServletResponse response) throws Exception {
        // 读取模版
        File file = ResourceUtils.getFile("classpath:excel_template/userInfo_easyPOI.xlsx");

        User user = new User();
        user.setId(id);
        user = userMapper.selectOne(user);
        // 将对象转成Map数据
        Map<String, Object> map = EntityUtils.entityToMap(user);
        // 处理图片
        ImageEntity image = new ImageEntity();
        image.setUrl(user.getPhoto());
        image.setColspan(2); // 列
        image.setRowspan(4); // 行
        map.put("photo", image);

        org.apache.poi.ss.usermodel.Workbook workbook =
                ExcelExportUtil.exportExcel(new TemplateExportParams(file.getPath()), map);

        String fileName = "用户数据.xlsx";
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        // 写入数据
        workbook.write(outputStream);
        // 释放资源
        outputStream.close();
        workbook.close();
    }

    private void downLoadCSVWay(HttpServletResponse response) throws IOException {
        String fileName = "使用csv文件百万数据导出测试.xlsx";
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("text/csv");
        try(
            // 1、获取输出流，并创建CSVWriter对象
            ServletOutputStream outputStream = response.getOutputStream();
            CSVWriter cw = new CSVWriter(new OutputStreamWriter(outputStream, StandardCharsets.UTF_8));
        ) {
            // 处理标题
            String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
            cw.writeNext(titles);

            int pageNum = 1;
            int pageSize = 50000;
            while (true) {
                List<User2> userList = user2Mapper.findPage((pageNum - 1) * pageSize, pageSize);
                if (CollectionUtils.isEmpty(userList)) {
                    break;
                }
                List<String[]> collect = userList.stream().map(user ->
                        new String[]{String.valueOf(user.getId()), user.getUserName(), user.getPhone(), String.valueOf(user.getHireDate()), user.getAddress()}
                ).collect(Collectors.toList());
                cw.writeAll(collect);
                cw.flush();
                pageNum++;
            }
        }
    }

    private void downLoadEasyPOIWay(HttpServletResponse response) throws Exception {
        ServletOutputStream outputStream = response.getOutputStream();
        String filename="百万数据.csv";
        //  设置两个头 一个是文件的打开方式 一个是mime类型
        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/csv");

        CsvExportParams csvExportParams = new CsvExportParams();
        // 排除掉实体类上的列
        csvExportParams.setExclusions(new String[]{"照片"});
        // 导出的数据
        List<User> userList = userMapper.selectAll();
        // csv导出
        CsvExportUtil.exportCsv(csvExportParams, User.class, userList, outputStream);
        outputStream.close();
    }

    private void downloadContractByPOI(Long id, HttpServletResponse response) throws FileNotFoundException {
        // 读取模版
        File file = ResourceUtils.getFile("classpath:word_template/contract_template.docx");
        try (
            XWPFDocument xf = new XWPFDocument(new FileInputStream(file));
        ) {
            // 查询数据
            User user = new User();
            user.setId(id);
            user = userMapper.selectOne(user);

            Map<String, String> mapData = new HashMap<>();
            mapData.put("userName", user.getUserName());
            mapData.put("hireDate", user.getHireDate().toString());
            mapData.put("address", user.getAddress());

            // 替换数据
            // 处理正文
            List<XWPFParagraph> paragraphs = xf.getParagraphs();
            for (XWPFParagraph paragraph : paragraphs) {
                // 获取每一个片段
                Set<String> keySet = mapData.keySet();
                for (String key : keySet) {
                    if (paragraph.getText().contains(key)) {
                        List<XWPFRun> runs = paragraph.getRuns();
                        for (int i = 0; i < runs.size(); i++) {
                            XWPFRun run = runs.get(i);
                            if (run.text().contains(key)) {
                                String text = run.text().replace(key, mapData.get(key));
                                run.setText(text, 0);
                            }
                        }
                    }
                }
            }
            // 资源目录  中文问题
            // String classpath = ResourceUtils.getURL("classpath:").getPath();
            // 处理表格
            Long userId = user.getId();
            Resource resource = new Resource();
            resource.setUserId(userId);
            List<Resource> resourceList = resourceMapper.select(resource);
            // 遍历处理
            if (resourceList != null && resourceList.size() > 0) {
                XWPFTable table = xf.getTables().get(0);
                XWPFTableRow row = table.getRow(0);

                int rowIndex = 1;
                for (Resource res : resourceList) {
                    // 添加行
                    this.copyRow(table, row, rowIndex);
                    XWPFTableRow r = table.getRow(rowIndex);
                    r.getCell(0).setText(res.getName());
                    r.getCell(1).setText(res.getPrice().toString());
                    r.getCell(2).setText(res.getNeedReturn()?"需要":"不需要");
                    File imageFile = ResourceUtils.getFile("classpath:static/" + res.getPhoto());
                    this.setCellImage(r.getCell(3), imageFile);
                    rowIndex++;
                }
            }

            // 导出word
            String fileName = "POI操作word.docx";
            // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
            response.setHeader( "Content-Disposition", "attachment;filename="
                    + new String(fileName.getBytes(),"ISO8859-1"));
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            xf.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void downloadContractByEasyPOI(Long id, HttpServletResponse response) throws Exception {
        /*
            easyPOI在导出word时，发现导出的表格中没有我们预想的图片，所以easyPOI不是万能的，如果有同样的需求还是得用POI来做！
            图片可以放在正文，但是表格不支图片
         */
        File file = ResourceUtils.getFile("classpath:word_template/contract_template_easyPOI.docx");
        // 准备数据
        User user = new User();
        user.setId(id);
        user = userMapper.selectOne(user);

        Resource resource = new Resource();
        resource.setUserId(user.getId());
        List<Resource> resourceList = resourceMapper.select(resource);
        user.setResourceList(resourceList);
        // 参数是map
        Map<String, Object> entityToMap = EntityUtils.entityToMap(user);
        XWPFDocument document = WordExportUtil.exportWord07(file.getPath(), entityToMap);

        // 导出word
        String fileName = "EasyPOI操作word.docx";
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename="
                + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        document.write(response.getOutputStream());
    }

    private void downLoadWithEasyPOIWay(HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams("员工数据", "sheet1", ExcelType.XSSF);
        // 所有数据
        List<User> userList = userMapper.selectAll();
        // EasyPoi导出
        org.apache.poi.ss.usermodel.Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, userList);

        String fileName = "用户详细数据.xlsx";
        // 文件的导出 一个流两个头（文件的打开方式 in-line、attachment，文件的下载类型mime）
        response.setHeader( "Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        ServletOutputStream outputStream = response.getOutputStream();
        // 写入数据
        workbook.write(outputStream);
        // 释放资源
        outputStream.close();
        workbook.close();
    }

    // 使用jasper模版 数据从后台查询
    private void downLoadByBackEnd(HttpServletResponse response) throws Exception {
        // 获取模版
        File file = ResourceUtils.getFile("classpath:pdf_template/userList-backEnd.jasper");
        FileInputStream fis = new FileInputStream(file);
        // 查询数据，并进行封装
        List<User> userList = userMapper.selectAll();
        JRBeanCollectionDataSource dataSource = new JRBeanCollectionDataSource(userList);
        JasperPrint jasperPrint = JasperFillManager.fillReport(fis, new HashMap<>(), dataSource);

        ServletOutputStream os = response.getOutputStream();
        response.setContentType("application/pdf");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String("用户数据.pdf".getBytes(), "ISO8859-1"));
        JasperExportManager.exportReportToPdfStream(jasperPrint, os);
        fis.close();
        os.close();
    }

    // 使用jasper模版 数据在模版中以SQL形式查询
    private void downLoadByDB(HttpServletResponse response) throws Exception {
        // 获取模版
        File file = ResourceUtils.getFile("classpath:pdf_template/userList.jasper");
        FileInputStream fis = new FileInputStream(file);
        JasperPrint jasperPrint = JasperFillManager.fillReport(fis, new HashMap<>(), hikariDataSource.getConnection());
        // 获取输出流
        ServletOutputStream os = response.getOutputStream();
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String("用户数据.pdf".getBytes(), "iso8859-1"));
        response.setContentType("application/pdf");
        JasperExportManager.exportReportToPdfStream(jasperPrint, os);
        // 关闭
        fis.close();
        os.close();
    }

    // 使用jasper模版 分组示例
    private void downLoadXlsxByJasperGroup(HttpServletResponse response) throws Exception {
        // 获取模版
        File file = ResourceUtils.getFile("classpath:pdf_template/userList-backEnd-group.jasper");
        // 获取数据集合
        Example example = new Example(User.class);
        example.setOrderByClause("province");
        List<User> userList = userMapper.selectByExample(example);

        response.setContentType("application/pdf");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String("用户数据.pdf".getBytes(), "ISO8859-1"));

        // 输出、输入流
        FileInputStream fis = new FileInputStream(file);
        ServletOutputStream os = response.getOutputStream();

        JRBeanCollectionDataSource dataSource = new JRBeanCollectionDataSource(userList);
        JasperPrint jasperPrint = JasperFillManager.fillReport(fis, new HashMap<>(), dataSource);
        JasperExportManager.exportReportToPdfStream(jasperPrint, os);

        fis.close();
        os.close();
    }

    // 使用jasper模版 展示用户详情 图片处理示例
    private void downLoadWithJasper(Long id, HttpServletResponse response) throws Exception {
        // 获取模版
        File file = ResourceUtils.getFile("classpath:pdf_template/userDetailInfo.jasper");
        String classpath = ResourceUtils.getFile("classpath:").getPath();
        // 数据
        User user = new User();
        user.setId(id);
        user = userMapper.selectOne(user);
        // 转换成Map填充
        Map<String, Object> objectMap = EntityUtils.entityToMap(user);
        objectMap.put("photo", classpath + "/" + objectMap.get("photo"));
        response.setContentType("application/pdf");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String("用户详细数据.pdf".getBytes(), "ISO8859-1"));
        FileInputStream fis = new FileInputStream(file);
        ServletOutputStream os = response.getOutputStream();

        JasperPrint jasperPrint = JasperFillManager.fillReport(fis, objectMap, new JREmptyDataSource());
        JasperExportManager.exportReportToPdfStream(jasperPrint, os);

        fis.close();
        os.close();
    }

}
